set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

with dm_whole_decide_duty_dt as (
    select
         waybill_no                                 --运单号
        ,platform_effect                            --自有平台时效标准(兜底路由)
        ,ziyou_platform_effect                      --自有平台时效标准(推荐路由)
        ,if_platform_standard                       --自有平台时效是否达成:1达成,0未达成(推荐路由)
        ,if_doudi_platform_standard                 --自有平台时效是否达成:1达成,0未达成(兜底路由)
        ,cainiao_platform_name                      --七星潭平台名称
        ,cainiao_platform_effect                    --七星潭平台时效标准
        ,if_cainiao_platform_pres_standard          --七星潭平台时效是否达成:1达成,0未达成
        ,first_duty_type                            --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
        ,first_ziyou_duty_type                      --自有平台静态责任:只有地址解析错误
        ,cainiao_plan_sign_time                     --七星潭规划签收时间
        ,ziyou_plan_sign_time                       --自有平台规划签收时间
        ,order_source_code                          --订单来源编码
        ,order_source_name                          --订单来源名称
        ,if_intercept                               --是否拦截件:1是,0否
        ,if_reback                                  --是否退回件:1是,0否
        ,if_transfer                                --是否转寄件:1是,0否 
        ,if_taking_off                              --是否截单后揽收
        ,if_taitc                                   --问题件抬头错标识
        ,if_reback_reach                            --判责退转拦截件是否达成
        ,start_city_code                            --始发城市编码
        ,start_city_name                            --始发城市名称
        ,sign_city_code                             --签收城市编码
        ,sign_city_name                             --签收城市名称
        ,taking_time                                --揽收时间
        ,deliver_time                               --出仓时间
        ,aging_sign_time                            --时效签收时间
        ,actual_sign_time                           --实际签收时间
        ,reback_apply_time                          --退转件申请时间
        ,intercept_registration_time                --拦截件登记时间
        ,economic_circle_name                       --经济圈名称
        ,scan_site_no                               --当前站点顺序号
        ,scan_site_code                             --当前站点编码
        ,scan_site_name                             --当前站点名称
        ,scan_site_type                             --当前站点类型
        ,scan_site_arrival_time                     --当前站点到件时间
        ,scan_site_send_time                        --当前站点发件时间
        ,scan_site_actual_arrival_time              --当前站点实际到车时间
        ,scan_site_actual_send_time                 --当前站点实际发车时间
        ,scan_site_seal_time                        --当前站点封车时间
        ,scan_site_planned_departure_time           --当前站点计划发车时间
        ,scan_site_planned_arrival_time             --当前站点计划到车时间
        ,scan_site_actual_shipment_no               --当前站点实际发车任务号
        ,scan_site_actual_shipment_name             --当前站点实际发车任务名
        ,scan_site_arrival_bagg_code                --当前站点到件包号
        ,scan_site_arrival_bagg_name                --当前站点到件包名
        ,scan_site_send_bagg_code                   --当前站点发件包号
        ,scan_site_send_bagg_name                   --当前站点发件包名
        ,scan_site_send_bagg_addr_code              --当前站发件建包目的地编码
        ,scan_site_send_bagg_addr_name              --当前站发件建包目的地名称
        ,scan_site_arrival_bagg_addr_code           --当前站到件建包目的地编码
        ,scan_site_arrival_bagg_addr_name           --当前站到件建包目的地名称
        ,scan_site_if_stop                          --当前站点是否经停
        ,next_site_code                             --当前站点实际下一站编码
        ,next_site_name                             --当前站点实际下一站名称
        ,next_site_type                             --当前站点实际下一站类型
        ,actual_diff_time                           --当前站点与下一站实际时间差(小时)
        ,actual_node                                --实际节点'
        ,actual_effect                              --动态时效'
        ,first_network_code                         --始发网点编码
        ,first_network_name                         --始发网点名称
        ,sign_network_code                          --签收网点编码
        ,sign_network_name                          --签收网点名称
        ,last_network_code                          --目的网点编码
        ,last_network_name                          --目的网点名称
        ,start_center_code                          --始发中心编码
        ,start_center_name                          --始发中心名称
        ,end_center_code                            --目的中心编码
        ,end_center_name                            --目的中心名称
        ,plan_last_taking_time                      --规划最晚揽收时间
        ,plan_sign_time                             --规划签收时间
        ,scan_site_plan_send_time                   --当前站点规划发车时间
        ,scan_site_plan_arrival_time                --当前站点规划到车时间
        ,scan_site_if_static                        --当前站点是否静态
        ,next_site_if_static                        --下一站点是否静态
        ,total_days                                 --T+时效
        ,route_id                                   --路由唯一键
        ,case when duty_no = 1 then duty_type end as duty_type
        ,case when ziyou_duty_no = 1 then ziyou_duty_type end as ziyou_duty_type
        ,customer_code                              --客户编码  20230824新增
        ,customer_name                              --客户名称
        ,scan_site_bagg_user_code                   --当前站点建包扫描员编号
        ,scan_site_bagg_user_name                   --当前站点建包扫描员名称
        ,pre_site_code --当前站点实际上一站编码
        ,pre_site_name --当前站点实际上一站名称
        ,pre_site_type --当前站点实际上一站类型
		,scan_site_ship_table_type  --当前站点发件任务号任务类型1干线,2支线
        ,if_static_gx               --是否静态干线
        ,dt
    from (
        select
             *
            ,row_number() over(partition by waybill_no order by case when duty_type = '退转件' then 0.1 when duty_type = '截单后揽收' then 0.2
                                                                     when duty_type = '签收晚点' then 0.3 when duty_type is null then 999
                                                                     else scan_site_no end) as duty_no --七星潭首责逻辑
            ,row_number() over(partition by waybill_no order by case when ziyou_duty_type = '退转件' then 0.1 when ziyou_duty_type = '截单后揽收' then 0.2
                                                                     when ziyou_duty_type = '签收晚点' then 0.3 when ziyou_duty_type is null then 999
                                                                     else scan_site_no end) as ziyou_duty_no --自有平台首责逻辑
        from (
            select
                 waybill_no                                 --运单号
                ,platform_effect                            --自有平台时效标准(兜底路由)
                ,ziyou_platform_effect                      --自有平台时效标准(推荐路由)
                ,if_platform_standard                       --自有平台时效是否达成:1达成,0未达成(推荐路由)
                ,if_doudi_platform_standard                 --自有平台时效是否达成:1达成,0未达成(兜底路由)
                ,cainiao_platform_name                      --七星潭平台名称
                ,cainiao_platform_effect                    --七星潭平台时效标准
                ,if_cainiao_platform_pres_standard          --七星潭平台时效是否达成:1达成,0未达成
                ,first_duty_type                            --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
                ,first_ziyou_duty_type                      --自有平台静态责任:只有地址解析错误
                ,cainiao_plan_sign_time                     --七星潭规划签收时间
                ,ziyou_plan_sign_time                       --自有平台规划签收时间
                ,order_source_code                          --订单来源编码
                ,order_source_name                          --订单来源名称
                ,if_intercept                               --是否拦截件:1是,0否
                ,if_reback                                  --是否退回件:1是,0否
                ,if_transfer                                --是否转寄件:1是,0否
                ,if_taking_off                              --是否截单后揽收
                ,if_taitc                                   --问题件抬头错标识
                ,if_reback_reach                            --判责退转拦截件是否达成
                ,start_city_code                            --始发城市编码
                ,start_city_name                            --始发城市名称
                ,sign_city_code                             --签收城市编码
                ,sign_city_name                             --签收城市名称
                ,taking_time                                --揽收时间
                ,deliver_time                               --出仓时间
                ,aging_sign_time                            --时效签收时间
                ,actual_sign_time                           --实际签收时间
                ,reback_apply_time                          --退转件申请时间
                ,intercept_registration_time                --拦截件登记时间
                ,economic_circle_name                       --经济圈名称
                ,row_number() over(partition by waybill_no order by
                                    case when scan_site_no = 1 then '0000-00-00 00:00:00'
                                         when scan_site_no = max(scan_site_no) over(partition by waybill_no)
                                          and (scan_site_code = last_network_code or scan_site_if_static = 1) then '9999-99-99 99:99:99'
                                         else coalesce(scan_site_arrival_time,scan_site_actual_arrival_time,scan_site_actual_send_time,scan_site_send_time)
                                         end ) as scan_site_no --当前站点顺序号
                ,scan_site_code                             --当前站点编码
                ,scan_site_name                             --当前站点名称
                ,scan_site_type                             --当前站点类型
                ,scan_site_arrival_time                     --当前站点到件时间
                ,scan_site_send_time                        --当前站点发件时间
                ,scan_site_actual_arrival_time              --当前站点实际到车时间
                ,scan_site_actual_send_time                 --当前站点实际发车时间
                ,scan_site_seal_time                        --当前站点封车时间
                ,scan_site_planned_departure_time           --当前站点计划发车时间
                ,scan_site_planned_arrival_time             --当前站点计划到车时间
                ,scan_site_actual_shipment_no               --当前站点实际发车任务号
                ,scan_site_actual_shipment_name             --当前站点实际发车任务名
                ,scan_site_arrival_bagg_code                --当前站点到件包号
                ,scan_site_arrival_bagg_name                --当前站点到件包名
                ,scan_site_send_bagg_code                   --当前站点发件包号
                ,scan_site_send_bagg_name                   --当前站点发件包名
                ,scan_site_send_bagg_addr_code              --当前站发件建包目的地编码
                ,scan_site_send_bagg_addr_name              --当前站发件建包目的地名称
                ,scan_site_arrival_bagg_addr_code           --当前站到件建包目的地编码
                ,scan_site_arrival_bagg_addr_name           --当前站到件建包目的地名称
                ,scan_site_if_stop                          --当前站点是否经停
                ,case when scan_site_if_stop = 1 then next_site_stop_code else next_site_code end as next_site_code --当前站点实际下一站编码
                ,case when scan_site_if_stop = 1 then next_site_stop_name else next_site_name end as next_site_name --当前站点实际下一站名称
                ,case when scan_site_if_stop = 1 then next_site_stop_type else next_site_type end as next_site_type --当前站点实际下一站类型
                ,actual_diff_time                           --当前站点与下一站实际时间差(小时)
                ,actual_node                                --实际节点'
                ,actual_effect                              --动态时效'
                ,first_network_code                         --始发网点编码
                ,first_network_name                         --始发网点名称
                ,sign_network_code                          --签收网点编码
                ,sign_network_name                          --签收网点名称
                ,last_network_code                          --目的网点编码
                ,last_network_name                          --目的网点名称
                ,start_center_code                          --始发中心编码
                ,start_center_name                          --始发中心名称
                ,end_center_code                            --目的中心编码
                ,end_center_name                            --目的中心名称
                ,plan_last_taking_time                      --规划最晚揽收时间
                ,plan_sign_time                             --规划签收时间
                ,scan_site_plan_send_time                   --当前站点规划发车时间
                ,scan_site_plan_arrival_time                --当前站点规划到车时间
                ,scan_site_if_static                        --当前站点是否静态
                ,next_site_if_static                        --下一站点是否静态
                ,total_days                                 --T+时效
                ,route_id                                   --路由唯一键
                ,customer_code                              --客户编码  20230824新增
                ,customer_name                              --客户名称
                ,scan_site_bagg_user_code                   --当前站点建包扫描员编号
                ,scan_site_bagg_user_name                   --当前站点建包扫描员名称
                ,case when scan_site_if_stop = 1 then pre_site_stop_code else pre_site_code end as pre_site_code --当前站点实际上一站编码
                ,case when scan_site_if_stop = 1 then pre_site_stop_name else pre_site_name end as pre_site_name --当前站点实际上一站名称
                ,case when scan_site_if_stop = 1 then pre_site_stop_type else pre_site_type end as pre_site_type --当前站点实际上一站类型
				,scan_site_ship_table_type  --当前站点发件任务号任务类型1干线,2支线
                ,if_static_gx               --是否静态干线


                ,case --如果已达成菜鸟时效标准,则不判责
                      when if_cainiao_platform_pres_standard = 1 then null
                      --如果有静态环节责任,则动态环节责任为空
                      when first_duty_type is not null then null
                      --退转件后达成,如果有静态环节责任,则动态环节为空,否则则判始发网点为退转件
                      when scan_site_no = 1 and first_duty_type is not null and if_reback_reach = 1 then null
                      when scan_site_no = 1 and first_duty_type is null and if_reback_reach = 1 then '退转件'
                      --始发网点截单后揽收
                      when scan_site_no = 1 and if_taking_off = 1 and scan_site_code = first_network_code
                       and ziyou_platform_effect + 1 > cainiao_platform_effect then '截单后揽收'
                      --建包错误优先级最高
                      when scan_site_send_bagg_addr_code <> lasted_center_code and scan_site_if_bagg = 1 then '建包错误' --20230330调整建包错误优先级
                      --始发网点错交
                      when type in(1,2,3) and next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code and first_center_actual_arrival_time > first_center_plan_last_arrival_time))
                           then '错交' --20230921修改规则,不管顺序对不对，中心到晚了才判错交，否则不判
                      --始发网点交件不准点(网点-集散-中心)
                      when type = 1 and scan_site_type = 6 and first_center_actual_arrival_time > first_center_plan_last_arrival_time then
                           case when first_center_actual_arrival_time is not null and next_site_actual_arrival_time is not null and next_site_actual_arrival_time > next_site_plan_last_arrival_time then '交件不准点'
                                when first_center_actual_arrival_time is not null and scan_site_actual_send_time is null and next_site_actual_arrival_time is null and next_site_actual_send_time is null then '交件不准点'
                                when first_center_actual_arrival_time is not null and scan_site_actual_send_time is null and next_site_actual_arrival_time is null and next_site_actual_send_time > next_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and next_site_actual_arrival_time  > next_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and scan_site_actual_send_time is null and next_site_actual_arrival_time is null and next_site_actual_send_time is null then '交件不准点'
                                end
                      --始发网点交件不准点(网点-中心)
                      when type = 2 and scan_site_type = 6 and first_center_actual_arrival_time > first_center_plan_last_arrival_time then
                           case when first_center_actual_arrival_time is not null and first_center_actual_arrival_time > first_center_plan_last_arrival_time  then '交件不准点'
                                when scan_site_actual_arrival_time is not null and first_center_actual_send_time > first_center_plan_send_time  then '交件不准点'
                                end
                      --始发网点最新轨迹交件不准点 --20230322新增
                      when type = 11 and scan_site_type = 6 then '交件不准点'
                      --始发网点交件不准点,下一站是最新轨迹 --20230322新增
                      when type in (1,2) and scan_site_type = 6 and next_l2_site_code is null and next_site_type = 5 then
                           case when next_site_actual_arrival_time > next_site_plan_last_arrival_time then '交件不准点'
                                when next_site_actual_arrival_time is null and next_site_actual_send_time > next_site_plan_send_time then '交件不准点'
                                end
                      --始发集散交件不准点
                      when type = 3 and scan_site_type = 5 and first_center_actual_arrival_time > first_center_plan_last_arrival_time then
                           case when first_center_actual_arrival_time is not null and scan_site_actual_send_time is not null and scan_site_actual_arrival_time <= scan_site_plan_arrival_time then '交件不准点'
                                when first_center_actual_arrival_time is not null and pre_site_actual_send_time is not null and scan_site_actual_arrival_time is null then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and scan_site_actual_arrival_time is null and scan_site_actual_send_time is not null and scan_site_actual_send_time > scan_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and pre_site_actual_send_time is not null and scan_site_actual_arrival_time is null and scan_site_actual_send_time is null then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and pre_site_actual_send_time is null and scan_site_actual_arrival_time is not null and scan_site_actual_send_time is not null and scan_site_actual_arrival_time <= scan_site_plan_last_arrival_time and scan_site_actual_send_time > scan_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and pre_site_actual_send_time is null and scan_site_actual_arrival_time is not null and scan_site_actual_send_time is null and scan_site_actual_arrival_time <= scan_site_plan_last_arrival_time then '交件不准点'
                                end
                      --始发集散最新轨迹交件不准点 --20230322新增
                      when type = 13 and scan_site_type = 5 then
                           case when scan_site_actual_arrival_time <= scan_site_plan_arrival_time then '交件不准点'
                                when scan_site_actual_arrival_time is null and scan_site_actual_send_time <= scan_site_plan_send_time then '交件不准点'
                                else '交件不准点'
                                end
                      --始发中心/中转中心错发
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code) then '错发' --2中转环节
                      --始发中心/中转中心出港转运不及时
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and next_site_actual_arrival_time >  from_unixtime(unix_timestamp(next_site_plan_last_arrival_time) - next_site_plan_oper_time * 60,'yyyy-MM-dd HH:mm:ss') and (scan_site_actual_send_time > scan_site_plan_send_time or scan_site_actual_send_time is null) then
                           case when scan_site_seal_time > scan_site_plan_send_time then '操作延误'
                                when (scan_site_seal_time <= scan_site_plan_send_time and scan_site_actual_send_time > scan_site_plan_send_time) or (scan_site_seal_time <= scan_site_plan_send_time and scan_site_actual_send_time is null) then '发车晚点'
                                when scan_site_seal_time is null then '操作延误'
                                end
                      --始发中心/中转中心错发，改为临时调度20230920
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and nvl(next_site_if_stop,0) = 1 and next_site_planned_arrival_time > next_site_plan_last_arrival_time then '临时调度'
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and scan_site_type = 4 and next_site_actual_arrival_time >  from_unixtime(unix_timestamp(next_site_plan_last_arrival_time) - next_site_plan_oper_time * 60,'yyyy-MM-dd HH:mm:ss') and scan_site_actual_send_time <= scan_site_plan_send_time then
                           case when nvl(next_site_if_stop,0) = 1 and next_stop_site_actual_send_time > next_stop_site_planned_departure_time then '运输晚点'
                                when nvl(next_site_if_stop,0) = 0 then '运输晚点'
                                end
                      --始发/中转中心最新轨迹 --20230322新增
                      when type in (14,15) and scan_site_type = 4 then
                           case when (scan_site_seal_time is null and scan_site_actual_send_time is null)
                                or (scan_site_seal_time is null and scan_site_actual_send_time > scan_site_plan_send_time)
                                or (scan_site_seal_time > scan_site_plan_send_time) then '操作延误'
                                when (scan_site_actual_send_time > scan_site_plan_send_time) or scan_site_actual_send_time is null then '发车晚点'
                                when scan_site_actual_send_time <= scan_site_plan_send_time then '运输晚点'
                                end
                      --中转中心经停点出港转运运输晚点
                      when scan_site_type in (4,5) and scan_site_if_stop = 1 and next_site_stop_actual_arrival_time > next_site_stop_plan_last_arrival_time then
                           case when pre_site_stop_actual_send_time <= pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time <= scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '发车晚点'
                                when pre_site_stop_actual_send_time <= pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time >  scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when pre_site_stop_actual_send_time >  pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time <= scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '发车晚点'
                                when pre_site_stop_actual_send_time >  pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time >  scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when pre_site_stop_actual_send_time <= pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time is null and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when pre_site_stop_actual_send_time >  pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time is null and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when scan_site_actual_send_time <= scan_site_planned_departure_time then '运输晚点'
                                end
                      --中转中心经停点最新轨迹 --20230322新增
                      when scan_site_if_stop = 1 and scan_site_type in (4,5) and next_site_stop_code is null then
                           case when (scan_site_seal_time is null and scan_site_actual_send_time is null)
                                or (scan_site_seal_time is null and scan_site_actual_send_time > scan_site_planned_departure_time)
                                or (scan_site_seal_time > scan_site_planned_departure_time) then '操作延误'     when (scan_site_actual_send_time > scan_site_planned_departure_time) or scan_site_actual_send_time is null then '发车晚点'
                                when scan_site_actual_send_time <= scan_site_planned_departure_time  is not null then '运输晚点'
                                end
                      --目的中心进港转运不及时
                      when type = 6 and scan_site_type = 4 then
                           case when scan_site_send_time is not null and next_site_arrival_time is not null and scan_site_send_time > scan_site_plan_send_time then '进港转运不及时'
                                when scan_site_send_time is null and next_site_arrival_time is null  then '进港转运不及时'
                                when scan_site_send_time is null and next_site_arrival_time is not null and next_site_arrival_time > next_site_plan_last_arrival_time then '进港转运不及时'
                                --目的中心错分
                                when next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code) then '错分' --3派件环节
                                end
                      --目的中心最新轨迹进港转运不及时 --20230322新增
                      when type = 17 and scan_site_type = 4 and (scan_site_send_time is null or scan_site_send_time > scan_site_plan_send_time) then '进港转运不及时'
                      --目的集散散货不及时
                      when type = 7 and scan_site_type = 5 then
                           case when pre_site_send_time is not null and pre_site_send_time <= pre_site_plan_send_time and (scan_site_send_time > scan_site_plan_send_time or scan_site_send_time is null) and next_site_actual_arrival_time > next_site_plan_last_arrival_time then '散货不及时'
                                when pre_site_send_time is not null and pre_site_send_time <= pre_site_plan_send_time and scan_site_arrival_time is null and (scan_site_send_time > scan_site_plan_send_time or scan_site_send_time is null) then '散货不及时'
                                --目的集散错分
                                when type = 7 and next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code) then '错分' --3派件环节
                                end
                      --目的集散最新轨迹 --20230322新增
                      when type = 18 and scan_site_type = 5 and (scan_site_send_time is null or scan_site_send_time > scan_site_plan_send_time) then '散货不及时'
                      --目的网点签收晚点
                      when type = 8 and scan_site_type = 6 and pre_site_send_time <= pre_site_plan_send_time then '签收晚点'
                      --目的集散/网点预测判责
                      when scan_site_if_static = 1 and pre_site_send_time <= pre_site_plan_send_time then
                           case when scan_site_type = 5 then '散货不及时' --目的集散预测判责
                                when scan_site_type = 6 then '签收晚点' --目的网点预测判责
                                end
                      end as duty_type --七星潭判责

            -------------------------------------------------------------------------------------------------------------------------------------------------
                ,case --如果已达成自有平台时效标准,则不判责
                      when if_platform_standard = 1 then null
                      --如果有静态环节责任,则动态环节责任为空
                      when first_ziyou_duty_type is not null then null
                      --退转件后达成,如果有静态环节责任,则动态环节为空,否则则判始发网点为退转件
                      when scan_site_no = 1 and first_ziyou_duty_type is not null and if_reback_reach = 1 then null
                      when scan_site_no = 1 and first_ziyou_duty_type is null and if_reback_reach = 1 then '退转件'
                      --始发网点截单后揽收 202300921 删除自有平台截单后揽收
                      --when scan_site_no = 1 and if_taking_off = 1 and scan_site_code = first_network_code
                      --and ((first_center_actual_arrival_time > first_center_plan_last_arrival_time)
                      --or (first_center_actual_arrival_time is null or first_center_actual_send_time is null)) then '截单后揽收'
                      --建包错误优先级最高
                      when scan_site_send_bagg_addr_code <> lasted_center_code and scan_site_if_bagg = 1 then '建包错误' --20230330调整建包错误优先级
                      --始发网点错交
                      when type in(1,2,3) and (next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code)
                           or (next_site_code in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code) and first_center_actual_arrival_time > first_center_plan_last_arrival_time))
                           then '错交' --20230921修改规则,不管顺序对不对，中心到晚了才判错交，否则不判
                      --始发网点交件不准点(网点-集散-中心)
                      when type = 1 and scan_site_type = 6 and first_center_actual_arrival_time > first_center_plan_last_arrival_time then
                           case when first_center_actual_arrival_time is not null and next_site_actual_arrival_time is not null and next_site_actual_arrival_time > next_site_plan_last_arrival_time then '交件不准点'
                                when first_center_actual_arrival_time is not null and scan_site_actual_send_time is null and next_site_actual_arrival_time is null and next_site_actual_send_time is null then '交件不准点'
                                when first_center_actual_arrival_time is not null and scan_site_actual_send_time is null and next_site_actual_arrival_time is null and next_site_actual_send_time > next_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and next_site_actual_arrival_time  > next_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and scan_site_actual_send_time is null and next_site_actual_arrival_time is null and next_site_actual_send_time is null then '交件不准点'
                                end
                      --始发网点交件不准点(网点-中心)
                      when type = 2 and scan_site_type = 6 and first_center_actual_arrival_time > first_center_plan_last_arrival_time then
                           case when first_center_actual_arrival_time is not null and first_center_actual_arrival_time > first_center_plan_last_arrival_time  then '交件不准点'
                                when scan_site_actual_arrival_time is not null and first_center_actual_send_time > first_center_plan_send_time  then '交件不准点'
                                end
                      --始发网点最新轨迹交件不准点 --20230322新增
                      when type = 11 and scan_site_type = 6 then '交件不准点'
                      --始发网点交件不准点,下一站是最新轨迹 --20230322新增
                      when type in (1,2) and scan_site_type = 6 and next_l2_site_code is null and next_site_type = 5 then
                           case when next_site_actual_arrival_time > next_site_plan_last_arrival_time then '交件不准点'
                                when next_site_actual_arrival_time is null and next_site_actual_send_time > next_site_plan_send_time then '交件不准点'
                                end
                      --始发集散交件不准点
                      when type = 3 and scan_site_type = 5 and first_center_actual_arrival_time > first_center_plan_last_arrival_time then
                           case when first_center_actual_arrival_time is not null and scan_site_actual_send_time is not null and scan_site_actual_arrival_time <= scan_site_plan_arrival_time then '交件不准点'
                                when first_center_actual_arrival_time is not null and pre_site_actual_send_time is not null and scan_site_actual_arrival_time is null then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and scan_site_actual_arrival_time is null and scan_site_actual_send_time is not null and scan_site_actual_send_time > scan_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and pre_site_actual_send_time is not null and scan_site_actual_arrival_time is null and scan_site_actual_send_time is null then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and pre_site_actual_send_time is null and scan_site_actual_arrival_time is not null and scan_site_actual_send_time is not null and scan_site_actual_arrival_time <= scan_site_plan_last_arrival_time and scan_site_actual_send_time > scan_site_plan_last_send_time then '交件不准点'
                                when first_center_actual_arrival_time is null and first_center_actual_send_time > first_center_plan_last_send_time and pre_site_actual_send_time is null and scan_site_actual_arrival_time is not null and scan_site_actual_send_time is null and scan_site_actual_arrival_time <= scan_site_plan_last_arrival_time then '交件不准点'
                                end
                      --始发集散最新轨迹交件不准点 --20230322新增
                      when type = 13 and scan_site_type = 5 then
                           case when scan_site_actual_arrival_time <= scan_site_plan_arrival_time then '交件不准点'
                                when scan_site_actual_arrival_time is null and scan_site_actual_send_time <= scan_site_plan_send_time then '交件不准点'
                                else '交件不准点'
                                end
                      --始发中心/中转中心错发
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code) then '错发' --2中转环节
                      --始发中心/中转中心出港转运不及时
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and next_site_actual_arrival_time >  from_unixtime(unix_timestamp(next_site_plan_last_arrival_time) - next_site_plan_oper_time * 60,'yyyy-MM-dd HH:mm:ss') and (scan_site_actual_send_time > scan_site_plan_send_time or scan_site_actual_send_time is null) then
                           case when scan_site_seal_time > scan_site_plan_send_time then '操作延误'
                                when (scan_site_seal_time <= scan_site_plan_send_time and scan_site_actual_send_time > scan_site_plan_send_time) or (scan_site_seal_time <= scan_site_plan_send_time and scan_site_actual_send_time is null) then '发车晚点'
                                when scan_site_seal_time is null then '操作延误'
                                end
                      --始发中心/中转中心错发，改为临时调度20230920
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and nvl(next_site_if_stop,0) = 1 and next_site_planned_arrival_time > next_site_plan_last_arrival_time then '临时调度'
                      when (type in (4,5) or scan_site_ship_table_type = 1 or if_static_gx = 1) and next_site_actual_arrival_time >  from_unixtime(unix_timestamp(next_site_plan_last_arrival_time) - next_site_plan_oper_time * 60,'yyyy-MM-dd HH:mm:ss') and scan_site_actual_send_time <= scan_site_plan_send_time then
                           case when nvl(next_site_if_stop,0) = 1 and next_stop_site_actual_send_time > next_stop_site_planned_departure_time then '运输晚点'
                                when nvl(next_site_if_stop,0) = 0 then '运输晚点'
                                end
                      --始发/中转中心最新轨迹 --20230322新增
                      when type in (14,15) and scan_site_type = 4 then
                           case when (scan_site_seal_time is null and scan_site_actual_send_time is null)
                                or (scan_site_seal_time is null and scan_site_actual_send_time > scan_site_plan_send_time)
                                or (scan_site_seal_time > scan_site_plan_send_time) then '操作延误'
                                when (scan_site_actual_send_time > scan_site_plan_send_time) or scan_site_actual_send_time is null then '发车晚点'
                                when scan_site_actual_send_time <= scan_site_plan_send_time then '运输晚点'
                                end
                      --中转中心经停点出港转运运输晚点
                      when scan_site_type in (4,5) and scan_site_if_stop = 1 and next_site_stop_actual_arrival_time > next_site_stop_plan_last_arrival_time then
                           case when pre_site_stop_actual_send_time <= pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time <= scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '发车晚点'
                                when pre_site_stop_actual_send_time <= pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time >  scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when pre_site_stop_actual_send_time >  pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time <= scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '发车晚点'
                                when pre_site_stop_actual_send_time >  pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time >  scan_site_planned_departure_time and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when pre_site_stop_actual_send_time <= pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time is null and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when pre_site_stop_actual_send_time >  pre_site_stop_planned_departure_time and scan_site_actual_arrival_time <= scan_site_planned_arrival_time and  scan_site_seal_time is null and scan_site_actual_send_time > scan_site_planned_departure_time then '操作延误'
                                when scan_site_actual_send_time <= scan_site_planned_departure_time then '运输晚点'
                                end
                      --中转中心经停点最新轨迹 --20230322新增
                      when scan_site_if_stop = 1 and scan_site_type in (4,5) and next_site_stop_code is null then
                           case when (scan_site_seal_time is null and scan_site_actual_send_time is null)
                                or (scan_site_seal_time is null and scan_site_actual_send_time > scan_site_planned_departure_time)
                                or (scan_site_seal_time > scan_site_planned_departure_time) then '操作延误'
                                when (scan_site_actual_send_time > scan_site_planned_departure_time) or scan_site_actual_send_time is null then '发车晚点'
                                when scan_site_actual_send_time <= scan_site_planned_departure_time  is not null then '运输晚点'
                                end
                      --目的中心进港转运不及时
                      when type = 6 and scan_site_type = 4 then
                           case when scan_site_send_time is not null and next_site_arrival_time is not null and scan_site_send_time > scan_site_plan_send_time then '进港转运不及时'
                                when scan_site_send_time is null and next_site_arrival_time is null  then '进港转运不及时'
                                when scan_site_send_time is null and next_site_arrival_time is not null and next_site_arrival_time > next_site_plan_last_arrival_time then '进港转运不及时'
                                --目的中心错分
                                when next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code) then '错分' --3派件环节
                                end
                      --目的中心最新轨迹进港转运不及时 --20230322新增
                      when type = 17 and scan_site_type = 4 and (scan_site_send_time is null or scan_site_send_time > scan_site_plan_send_time) then '进港转运不及时'
                      --目的集散散货不及时
                      when type = 7 and scan_site_type = 5 then
                           case when pre_site_send_time is not null and pre_site_send_time <= pre_site_plan_send_time and (scan_site_send_time > scan_site_plan_send_time or scan_site_send_time is null) and next_site_actual_arrival_time > next_site_plan_last_arrival_time then '散货不及时'
                                when pre_site_send_time is not null and pre_site_send_time <= pre_site_plan_send_time and scan_site_arrival_time is null and (scan_site_send_time > scan_site_plan_send_time or scan_site_send_time is null) then '散货不及时'
                                --目的集散错分
                                when type = 7 and next_site_code not in (next_site_plan_code,next_l2_site_plan_code,next_l3_site_plan_code,next_l4_site_plan_code) then '错分' --3派件环节
                                end
                      --目的集散最新轨迹 --20230322新增
                      when type = 18 and scan_site_type = 5 and (scan_site_send_time is null or scan_site_send_time > scan_site_plan_send_time) then '散货不及时'
                      --目的网点签收晚点
                      when type = 8 and scan_site_type = 6 and pre_site_send_time <= pre_site_plan_send_time then '签收晚点'
                      --目的集散/网点预测判责
                      when scan_site_if_static = 1 and pre_site_send_time <= pre_site_plan_send_time then
                           case when scan_site_type = 5 then '散货不及时' --目的集散预测判责
                                when scan_site_type = 6 then '签收晚点' --目的网点预测判责
                                end
                      end as ziyou_duty_type --自有平台判责
                ,dt
            from (
                select
                     dynamic.waybill_no                             --运单号
                    ,dynamic.ziyou_platform_effect                  --自有平台时效标准(推荐路由)
                    ,dynamic.if_platform_standard                   --自有平台时效是否达成:1达成,0未达成(推荐路由)
                    ,dynamic.if_doudi_platform_standard             --自有平台时效是否达成:1达成,0未达成(兜底路由)
                    ,dynamic.cainiao_platform_name                  --七星潭平台名称
                    ,dynamic.cainiao_platform_effect                --七星潭平台时效标准
                    ,dynamic.if_cainiao_platform_pres_standard      --七星潭平台时效是否达成:1达成,0未达成
                    ,max(dynamic.first_duty_type      ) over(partition by dynamic.waybill_no) as first_duty_type       --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
                    ,max(dynamic.first_ziyou_duty_type) over(partition by dynamic.waybill_no) as first_ziyou_duty_type --自由平台静态环节责任
                    ,dynamic.if_intercept                           --是否拦截件:1是,0否
                    ,dynamic.if_reback                              --是否退回件:1是,0否
                    ,dynamic.if_transfer                            --是否转寄件:1是,0否
                    ,dynamic.start_city_code                        --始发城市编码
                    ,dynamic.start_city_name                        --始发城市名称
                    ,dynamic.sign_city_code                         --签收城市编码
                    ,dynamic.sign_city_name                         --签收城市名称
                    ,dynamic.taking_time                            --揽收时间
                    ,dynamic.deliver_time                           --出仓时间
                    ,dynamic.aging_sign_time                        --时效签收时间
                    ,dynamic.actual_sign_time                       --实际签收时间
                    ,dynamic.economic_circle_name                   --经济圈名称
                    ,dynamic.scan_site_no                           --当前站点编号
                    ,dynamic.scan_site_code                         --当前站点编码
                    ,dynamic.scan_site_name                         --当前站点名称
                    ,dynamic.scan_site_type                         --当前站点类型
                    ,dynamic.scan_site_arrival_time                 --当前站点到件时间
                    ,dynamic.scan_site_send_time                    --当前站点发件时间
                    ,dynamic.scan_site_actual_arrival_time          --当前站点发件任务号实际到车时间
                    ,dynamic.scan_site_actual_send_time             --当前站点发件任务号实际发车时间
                    ,dynamic.scan_site_planned_departure_time       --当前站点发件任务号规划发车时间
                    ,dynamic.scan_site_planned_arrival_time         --当前站点发件任务号规划到车时间
                    ,dynamic.scan_site_seal_time                    --当前站点发件任务号封车时间
                    ,dynamic.scan_site_actual_shipment_no           --当前站点发件任务号实际发车任务号
                    ,dynamic.scan_site_actual_shipment_name         --当前站点发件任务号实际发车任务名
                    ,dynamic.scan_site_if_stop                      --当前站是否经停
                    ,dynamic.scan_site_if_bagg                      --当前站点是否建包扫描
                    ,dynamic.scan_site_arrival_bagg_code            --当前站点到件包号
                    ,dynamic.scan_site_arrival_bagg_name            --当前站点到件包名
                    ,dynamic.scan_site_send_bagg_code               --当前站点发件包号
                    ,dynamic.scan_site_send_bagg_name               --当前站点发件包名
                    ,dynamic.scan_site_send_bagg_addr_code          --当前站发件建包目的地编码
                    ,dynamic.scan_site_send_bagg_addr_name          --当前站发件建包目的地名称
                    ,dynamic.scan_site_arrival_bagg_addr_code       --当前站到件建包目的地编码
                    ,dynamic.scan_site_arrival_bagg_addr_name       --当前站到件建包目的地名称
                    ,dynamic.actual_diff_time                       --当前站点与下一站实际时间差(小时)
                    ,dynamic.actual_node                            --当前站点简称(实际节点)
                    ,dynamic.next_site_code                         --下一站编码
                    ,dynamic.next_site_name                         --下一站名称
                    ,dynamic.next_site_type                         --下一站类型
                    ,dynamic.next_site_arrival_time                 --下一站到件时间
                    ,dynamic.next_site_send_time                    --下一站发件时间
                    ,dynamic.next_site_actual_arrival_time          --下一站发件任务号到车时间
                    ,dynamic.next_site_actual_send_time             --下一站发件任务号发车时间
                    ,dynamic.next_site_seal_time                    --下一站发件任务号封车时间
                    ,dynamic.next_site_planned_departure_time       --下一站发件任务号规划发车时间
                    ,dynamic.next_site_planned_arrival_time         --下一站发件任务号规划到车时间
                    ,dynamic.next_site_if_stop                      --下一站发件是否经停
                    ,dynamic.pre_site_code                          --上一站编码
                    ,dynamic.pre_site_name                          --上一站名称
                    ,dynamic.pre_site_type                          --上一站类型
                    ,dynamic.pre_site_arrival_time                  --上一站到件时间
                    ,dynamic.pre_site_send_time                     --上一站发件时间
                    ,dynamic.pre_site_actual_arrival_time           --上一站发件任务号到车时间
                    ,dynamic.pre_site_actual_send_time              --上一站发件任务号发车时间
                    ,dynamic.pre_site_seal_time                     --上一站发件任务号封车时间
                    ,dynamic.pre_site_planned_departure_time        --上一站发件任务号规划发车时间
                    ,dynamic.pre_site_planned_arrival_time          --上一站发件任务号规划到车时间
                    ,dynamic.pre_scan_if_stop                       --上一站是否经停
                    ,dynamic.first_center_actual_arrival_time       --始发中心实际到车时间
                    ,dynamic.first_center_actual_send_time          --始发中心实际发车时间
                    ,dynamic.first_network_code                     --始发网点编码
                    ,dynamic.first_network_name                     --始发网点名称
                    ,dynamic.sign_network_code                      --签收网点编码
                    ,dynamic.sign_network_name                      --签收网点名称
                    ,dynamic.last_network_code                      --目的网点编码
                    ,dynamic.last_network_name                      --目的网点名称
                    ,dynamic.first_code                             --一段码
                    ,dynamic.lasted_center_code                     --一段码目的中心编码
                    ,dynamic.second_code                            --二段码
                    ,dynamic.lasted_network_code                    --二段码目的网点
                    ,dynamic.type                                   --场景划分
                    ,dynamic.if_taking_off                          --是否截单后揽收
                    ,dynamic.actual_effect                          --动态时效 20230216新增
                    ,dynamic.pre_site_stop_actual_send_time         --经停站点上一站实际发车时间 --20230227新增
                    ,dynamic.pre_site_stop_planned_departure_time   --经停站点上一站规划发车时间 --20230227新增
                    ,dynamic.next_stop_site_actual_send_time        --下一经停站点规划发车时间   --20230227新增
                    ,dynamic.next_stop_site_planned_departure_time  --下一经停站点规划到车时间   --20230227新增
                    ,dynamic.next_stop_site_actual_arrival_time     --下一经停站点实际到车时间   --20230227新增
                    ,dynamic.next_stop_site_planned_arrival_time    --下一经停站点实际发车时间   --20230227新增
                    ,dynamic.next_site_stop_code                    --
                    ,dynamic.next_site_stop_name                    --
                    ,dynamic.next_site_stop_type                    --
                    ,dynamic.next_site_stop_actual_arrival_time     --
                    ,dynamic.next_site_stop_planned_arrival_time    --
                    ,dynamic.next_l2_site_code                      --
                    ,dynamic.if_taitc                               --问题件抬头错标识
                    ,dynamic.order_source_code                      --订单来源编码
                    ,dynamic.order_source_name                      --订单来源名称
                    ,dynamic.if_reback_reach                        --判责退转件是否达成
                    ,dynamic.reback_apply_time                      --退转件申请时间
                    ,dynamic.intercept_registration_time            --拦截件登记时间
                    ,dynamic.cainiao_plan_sign_time                 --达成率菜鸟规划签收时间
                    ,dynamic.ziyou_plan_sign_time                   --达成率自有规划签收时间
                    ,dynamic.taking_date                            --截单后揽收日期
                    ,dynamic.start_center_code                      --始发中心编码
                    ,dynamic.start_center_name                      --始发中心名称
                    ,dynamic.end_center_code                        --目的中心编码
                    ,dynamic.end_center_name                        --目的中心名称
                    ,dynamic.scan_site_if_static                    --当前站点是否静态
                    ,dynamic.next_site_if_static                    --下一站点是否静态
                    ,dynamic.customer_code                          --客户编码  20230824新增
                    ,dynamic.customer_name                          --客户名称
                    ,dynamic.scan_site_bagg_user_code               --当前站点建包扫描员编号
                    ,dynamic.scan_site_bagg_user_name               --当前站点建包扫描员名称
                    ,static.route_id                                --路由唯一键
                    ,static.scan_site_plan_no                       --当前站点编号
                    ,static.scan_site_plan_code                     --当前站点编码
                    ,static.scan_site_plan_name                     --当前站点名称
                    ,static.scan_site_plan_type                     --当前站点类型
                    ,static.scan_site_plan_simple_name              --当前站点简称
                    ,static.scan_site_plan_send_time                --当前站点规划发车时间
                    ,static.scan_site_plan_arrival_time             --当前站点规划到车时间
                    ,static.scan_site_plan_last_arrival_time        --当前站点规划最晚到车时间
                    ,static.scan_site_plan_last_send_time           --当前站点规划最晚发车时间
                    ,static.plan_diff_time                          --当前站点与下一站点时间差
                    ,static.plan_last_taking_time                   --当前站点规划最晚揽收结束时间
                    ,static.plan_deliver_time                       --目的网点规划派件时间
                    ,static.plan_sign_time                          --目的网点规划签收时间
                    ,static.pre_site_plan_send_time                 --上一站点规划发车时间
                    ,static.pre_site_plan_arrival_time              --上一站点规划到车时间
                    ,static.pre_site_plan_last_send_time            --上一站点规划最晚发车时间
                    ,static.pre_site_plan_last_arrival_time         --上一站点规划最晚到车时间
                    ,static.next_site_plan_no                       --下一站点编号
                    ,static.next_site_plan_code                     --下一站点编码
                    ,static.next_site_plan_name                     --下一站点名称
                    ,static.next_site_plan_type                     --下一站点类型
                    ,nvl(static.next_l2_site_code,static.route_id) as next_l2_site_plan_code --下下一站点编码
                    ,nvl(static.next_l3_site_code,static.route_id) as next_l3_site_plan_code --下下下一站点编码
                    ,nvl(static.next_l4_site_code,static.route_id) as next_l4_site_plan_code --下下下下一站点编码
                    ,static.next_site_plan_send_time                --下一站点规划发车时间
                    ,static.next_site_plan_arrival_time             --下一站点规划到车时间
                    ,static.next_site_plan_last_send_time           --下一站点规划最晚发车时间
                    ,static.next_site_plan_last_arrival_time        --下一站点规划最晚到车时间
                    ,static.first_center_plan_send_time             --始发中心规划发车时间
                    ,static.first_center_plan_arrival_time          --始发中心规划到车时间
                    ,static.first_center_plan_last_send_time        --始发中心规划最晚发车时间
                    ,static.first_center_plan_last_arrival_time     --始发中心规划最晚到车时间
                    ,static.plan_node                               --规划节点
                    ,static.next_site_plan_oper_time                --下一站点规划操作时间(分钟)
                    ,static.total_days
                    ,static.total_days + if_taking_off as platform_effect        --时效标准
                    ,nvl(static.pre_site_code   ,static.route_id) as pre_site_plan_code     --上一站点编码
                    ,nvl(static.pre_l2_site_code,static.route_id) as pre_l2_site_plan_code  --上上一站点编码
                    ,nvl(static.pre_l3_site_code,static.route_id) as pre_l3_site_plan_code  --上上上一站点编码
                    ,case when dynamic.scan_site_if_stop = 1 then lead(static.scan_site_plan_last_arrival_time) over(partition by dynamic.waybill_no order by nvl(dynamic.scan_site_actual_arrival_time,dynamic.scan_site_actual_send_time)) end  as next_site_stop_plan_last_arrival_time --经停点下一站最晚到车时间
                    ,dynamic.customer_code                          --客户编码
                    ,dynamic.customer_name                          --客户名称
                    ,dynamic.scan_site_bagg_user_code               --当前站点建包扫描员编号
                    ,dynamic.scan_site_bagg_user_name               --当前站点建包扫描员名称
                    ,dynamic.pre_site_stop_code                     --经停站点上一站编码
                    ,dynamic.pre_site_stop_name                     --经停站点上一站名称
                    ,dynamic.pre_site_stop_type                     --经停站点上一站类型
                    ,dynamic.scan_site_ship_table_type              --当前站点发件任务号任务类型1干线,2支线
                    ,case when static.pre_site_plan_type = 4 and scan_site_plan_type = 5 and static.next_site_plan_type = 4 then 1 else 0 end as if_static_gx --是否静态干线
                    ,dynamic.dt
                from (
                    select
                         waybill_no                             --运单号
                        ,ziyou_platform_effect                  --自有平台时效标准(推荐路由)
                        ,if_platform_standard                   --自有平台时效是否达成:1达成,0未达成(推荐路由)
                        ,if_doudi_platform_standard             --自有平台时效是否达成:1达成,0未达成(兜底路由)
                        ,cainiao_platform_name                  --七星潭平台名称
                        ,cainiao_platform_effect                --七星潭平台时效标准
                        ,if_cainiao_platform_pres_standard      --七星潭平台时效是否达成:1达成,0未达成
                        ,first_duty_type                        --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
                        ,first_ziyou_duty_type                  --自由平台静态环节责任
                        ,if_intercept                           --是否拦截件:1是,0否
                        ,if_reback                              --是否退回件:1是,0否
                        ,if_transfer                            --是否转寄件:1是,0否
                        ,start_city_code                        --始发城市编码
                        ,start_city_name                        --始发城市名称
                        ,sign_city_code                         --签收城市编码
                        ,sign_city_name                         --签收城市名称
                        ,taking_time                            --揽收时间
                        ,deliver_time                           --出仓时间
                        ,aging_sign_time                        --时效签收时间
                        ,actual_sign_time                       --实际签收时间
                        ,economic_circle_name                   --经济圈名称
                        ,scan_site_no                           --当前站点编号
                        ,scan_site_code                         --当前站点编码
                        ,scan_site_name                         --当前站点名称
                        ,scan_site_type                         --当前站点类型
                        ,scan_site_arrival_time                 --当前站点到件时间
                        ,scan_site_send_time                    --当前站点发件时间
                        ,scan_site_actual_arrival_time          --当前站点发件任务号实际到车时间
                        ,scan_site_actual_send_time             --当前站点发件任务号实际发车时间
                        ,scan_site_planned_departure_time       --当前站点发件任务号规划发车时间
                        ,scan_site_planned_arrival_time         --当前站点发件任务号规划到车时间
                        ,scan_site_seal_time                    --当前站点发件任务号封车时间
                        ,scan_site_actual_shipment_no           --当前站点发件任务号实际发车任务号
                        ,scan_site_actual_shipment_name         --当前站点发件任务号实际发车任务名
                        ,scan_site_if_stop                      --当前站是否经停
                        ,scan_site_if_bagg                      --当前站点是否建包扫描
                        ,scan_site_arrival_bagg_code            --当前站点到件包号
                        ,scan_site_arrival_bagg_name            --当前站点到件包名
                        ,scan_site_send_bagg_code               --当前站点发件包号
                        ,scan_site_send_bagg_name               --当前站点发件包名
                        ,scan_site_send_bagg_addr_code          --当前站发件建包目的地编码
                        ,scan_site_send_bagg_addr_name          --当前站发件建包目的地名称
                        ,scan_site_arrival_bagg_addr_code       --当前站到件建包目的地编码
                        ,scan_site_arrival_bagg_addr_name       --当前站到件建包目的地名称
                        ,actual_diff_time                       --当前站点与下一站实际时间差(小时)
                        ,actual_node                            --当前站点简称(实际节点)
                        ,next_site_code                         --下一站编码
                        ,next_site_name                         --下一站名称
                        ,next_site_type                         --下一站类型
                        ,next_site_arrival_time                 --下一站到件时间
                        ,next_site_send_time                    --下一站发件时间
                        ,next_site_actual_arrival_time          --下一站发件任务号到车时间
                        ,next_site_actual_send_time             --下一站发件任务号发车时间
                        ,next_site_seal_time                    --下一站发件任务号封车时间
                        ,next_site_planned_departure_time       --下一站发件任务号规划发车时间
                        ,next_site_planned_arrival_time         --下一站发件任务号规划到车时间
                        ,next_site_if_stop                      --下一站发件是否经停
                        ,pre_site_code                          --上一站编码
                        ,pre_site_name                          --上一站名称
                        ,pre_site_type                          --上一站类型
                        ,pre_site_arrival_time                  --上一站到件时间
                        ,pre_site_send_time                     --上一站发件时间
                        ,pre_site_actual_arrival_time           --上一站发件任务号到车时间
                        ,pre_site_actual_send_time              --上一站发件任务号发车时间
                        ,pre_site_seal_time                     --上一站发件任务号封车时间
                        ,pre_site_planned_departure_time        --上一站发件任务号规划发车时间
                        ,pre_site_planned_arrival_time          --上一站发件任务号规划到车时间
                        ,pre_scan_if_stop                       --上一站是否经停
                        ,first_center_actual_arrival_time       --始发中心实际到车时间
                        ,first_center_actual_send_time          --始发中心实际发车时间
                        ,first_network_code                     --始发网点编码
                        ,first_network_name                     --始发网点名称
                        ,sign_network_code                      --签收网点编码
                        ,sign_network_name                      --签收网点名称
                        ,last_network_code                      --目的网点编码
                        ,last_network_name                      --目的网点名称
                        ,first_code                             --一段码
                        ,lasted_center_code                     --一段码目的中心编码
                        ,second_code                            --二段码
                        ,lasted_network_code                    --二段码目的网点
                        ,type                                   --场景划分
                        ,if_taking_off                          --是否截单后揽收
                        ,actual_effect                          --动态时效 20230216新增
                        ,pre_site_stop_actual_send_time         --经停站点上一站实际发车时间 --20230227新增
                        ,pre_site_stop_planned_departure_time   --经停站点上一站规划发车时间 --20230227新增
                        ,next_stop_site_actual_send_time        --下一经停站点规划发车时间   --20230227新增
                        ,next_stop_site_planned_departure_time  --下一经停站点规划到车时间   --20230227新增
                        ,next_stop_site_actual_arrival_time     --下一经停站点实际到车时间   --20230227新增
                        ,next_stop_site_planned_arrival_time    --下一经停站点实际发车时间   --20230227新增
                        ,next_site_stop_code                    --
                        ,next_site_stop_name                    --
                        ,next_site_stop_type                    --
                        ,next_site_stop_actual_arrival_time     --
                        ,next_site_stop_planned_arrival_time    --
                        ,next_l2_site_code                      --
                        ,if_taitc                               --问题件抬头错标识
                        ,order_source_code                      --订单来源编码
                        ,order_source_name                      --订单来源名称
                        ,if_reback_reach                        --判责退转件是否达成
                        ,reback_apply_time                      --退转件申请时间
                        ,intercept_registration_time            --拦截件登记时间
                        ,cainiao_plan_sign_time                 --达成率菜鸟规划签收时间
                        ,ziyou_plan_sign_time                   --达成率自有规划签收时间
                        ,taking_date                            --截单后揽收日期
                        ,start_center_code                      --始发中心编码
                        ,start_center_name                      --始发中心名称
                        ,end_center_code                        --目的中心编码
                        ,end_center_name                        --目的中心名称
                        ,0 as scan_site_if_static               --当前站点点是否静态
                        ,case when type in (17,18) then 1 else 0 end as next_site_if_static --下一站点是否静态
                        ,customer_code                          --客户编码
                        ,customer_name                          --客户名称
                        ,scan_site_bagg_user_code               --当前站点建包扫描员编号
                        ,scan_site_bagg_user_name               --当前站点建包扫描员名称
                        ,pre_site_stop_code                     --经停站点上一站编码
                        ,pre_site_stop_name                     --经停站点上一站名称
                        ,pre_site_stop_type                     --经停站点上一站类型
                        ,scan_site_ship_table_type              --当前站点发件任务号任务类型1干线,2支线
                        ,dt
                    from jms_tmp.dm_whole_decide_duty_dynamic_dt
                    where dt between date_sub('{{ execution_date | cst_ds }}',5) and date_add('{{ execution_date | cst_ds }}',5)
                    union all
                    select
                         dynamic.waybill_no                             --运单号
                        ,dynamic.ziyou_platform_effect                  --自有平台时效标准(推荐路由)
                        ,dynamic.if_platform_standard                   --自有平台时效是否达成:1达成,0未达成(推荐路由)
                        ,dynamic.if_doudi_platform_standard             --自有平台时效是否达成:1达成,0未达成(兜底路由)
                        ,dynamic.cainiao_platform_name                  --七星潭平台名称
                        ,dynamic.cainiao_platform_effect                --七星潭平台时效标准
                        ,dynamic.if_cainiao_platform_pres_standard      --七星潭平台时效是否达成:1达成,0未达成
                        ,dynamic.first_duty_type                        --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
                        ,dynamic.first_ziyou_duty_type                  --自由平台静态环节责任
                        ,dynamic.if_intercept                           --是否拦截件:1是,0否
                        ,dynamic.if_reback                              --是否退回件:1是,0否
                        ,dynamic.if_transfer                            --是否转寄件:1是,0否
                        ,dynamic.start_city_code                        --始发城市编码
                        ,dynamic.start_city_name                        --始发城市名称
                        ,dynamic.sign_city_code                         --签收城市编码
                        ,dynamic.sign_city_name                         --签收城市名称
                        ,dynamic.taking_time                            --揽收时间
                        ,dynamic.deliver_time                           --出仓时间
                        ,dynamic.aging_sign_time                        --时效签收时间
                        ,dynamic.actual_sign_time                       --实际签收时间
                        ,dynamic.economic_circle_name                   --经济圈名称
                        ,static.scan_site_plan_no   as scan_site_no     --当前站点编号
                        ,static.scan_site_plan_code as scan_site_code   --当前站点编码
                        ,static.scan_site_plan_name as scan_site_name   --当前站点名称
                        ,static.scan_site_plan_type as scan_site_type   --当前站点类型
                        ,null as scan_site_arrival_time                 --当前站点到件时间
                        ,null as scan_site_send_time                    --当前站点发件时间
                        ,null as scan_site_actual_arrival_time          --当前站点发件任务号实际到车时间
                        ,null as scan_site_actual_send_time             --当前站点发件任务号实际发车时间
                        ,null as scan_site_planned_departure_time       --当前站点发件任务号规划发车时间
                        ,null as scan_site_planned_arrival_time         --当前站点发件任务号规划到车时间
                        ,null as scan_site_seal_time                    --当前站点发件任务号封车时间
                        ,null as scan_site_actual_shipment_no           --当前站点发件任务号实际发车任务号
                        ,null as scan_site_actual_shipment_name         --当前站点发件任务号实际发车任务名
                        ,null as scan_site_if_stop                      --当前站是否经停
                        ,null as scan_site_if_bagg                      --当前站点是否建包扫描
                        ,null as scan_site_arrival_bagg_code            --当前站点到件包号
                        ,null as scan_site_arrival_bagg_name            --当前站点到件包名
                        ,null as scan_site_send_bagg_code               --当前站点发件包号
                        ,null as scan_site_send_bagg_name               --当前站点发件包名
                        ,null as scan_site_send_bagg_addr_code          --当前站发件建包目的地编码
                        ,null as scan_site_send_bagg_addr_name          --当前站发件建包目的地名称
                        ,null as scan_site_arrival_bagg_addr_code       --当前站到件建包目的地编码
                        ,null as scan_site_arrival_bagg_addr_name       --当前站到件建包目的地名称
                        ,null as actual_diff_time                       --当前站点与下一站实际时间差(小时)
                        ,static.plan_node as actual_node                --当前站点简称(实际节点)
                        ,null as next_site_code                         --下一站编码
                        ,null as next_site_name                         --下一站名称
                        ,null as next_site_type                         --下一站类型
                        ,null as next_site_arrival_time                 --下一站到件时间
                        ,null as next_site_send_time                    --下一站发件时间
                        ,null as next_site_actual_arrival_time          --下一站发件任务号到车时间
                        ,null as next_site_actual_send_time             --下一站发件任务号发车时间
                        ,null as next_site_seal_time                    --下一站发件任务号封车时间
                        ,null as next_site_planned_departure_time       --下一站发件任务号规划发车时间
                        ,null as next_site_planned_arrival_time         --下一站发件任务号规划到车时间
                        ,null as next_site_if_stop                      --下一站发件是否经停
                        ,dynamic.scan_site_code                      as pre_site_code                          --上一站编码
                        ,dynamic.scan_site_name                      as pre_site_name                          --上一站名称
                        ,dynamic.scan_site_type                      as pre_site_type                          --上一站类型
                        ,dynamic.scan_site_arrival_time              as pre_site_arrival_time                  --上一站到件时间
                        ,dynamic.scan_site_send_time                 as pre_site_send_time                     --上一站发件时间
                        ,dynamic.scan_site_actual_arrival_time       as pre_site_actual_arrival_time           --上一站发件任务号到车时间
                        ,dynamic.scan_site_actual_send_time          as pre_site_actual_send_time              --上一站发件任务号发车时间
                        ,dynamic.scan_site_seal_time                 as pre_site_seal_time                     --上一站发件任务号封车时间
                        ,dynamic.scan_site_planned_departure_time    as pre_site_planned_departure_time        --上一站发件任务号规划发车时间
                        ,dynamic.scan_site_planned_arrival_time      as pre_site_planned_arrival_time          --上一站发件任务号规划到车时间
                        ,dynamic.scan_site_if_stop                   as pre_scan_if_stop                       --上一站是否经停
                        ,dynamic.first_center_actual_arrival_time       --始发中心实际到车时间
                        ,dynamic.first_center_actual_send_time          --始发中心实际发车时间
                        ,dynamic.first_network_code                     --始发网点编码
                        ,dynamic.first_network_name                     --始发网点名称
                        ,dynamic.sign_network_code                      --签收网点编码
                        ,dynamic.sign_network_name                      --签收网点名称
                        ,dynamic.last_network_code                      --目的网点编码
                        ,dynamic.last_network_name                      --目的网点名称
                        ,dynamic.first_code                             --一段码
                        ,dynamic.lasted_center_code                     --一段码目的中心编码
                        ,dynamic.second_code                            --二段码
                        ,dynamic.lasted_network_code                    --二段码目的网点
                        ,dynamic.type                                   --场景划分**************这里的类型是上一站的类型!!!
                        ,dynamic.if_taking_off                          --是否截单后揽收
                        ,dynamic.actual_effect                          --动态时效 20230216新增
                        ,null as pre_site_stop_actual_send_time         --经停站点上一站实际发车时间 --20230227新增
                        ,null as pre_site_stop_planned_departure_time   --经停站点上一站规划发车时间 --20230227新增
                        ,null as next_stop_site_actual_send_time        --下一经停站点规划发车时间   --20230227新增
                        ,null as next_stop_site_planned_departure_time  --下一经停站点规划到车时间   --20230227新增
                        ,null as next_stop_site_actual_arrival_time     --下一经停站点实际到车时间   --20230227新增
                        ,null as next_stop_site_planned_arrival_time    --下一经停站点实际发车时间   --20230227新增
                        ,null as next_site_stop_code                    --
                        ,null as next_site_stop_name                    --
                        ,null as next_site_stop_type                    --
                        ,null as next_site_stop_actual_arrival_time     --
                        ,null as next_site_stop_planned_arrival_time    --
                        ,null as next_l2_site_code                      --
                        ,dynamic.if_taitc                               --问题件抬头错标识
                        ,dynamic.order_source_code                      --订单来源编码
                        ,dynamic.order_source_name                      --订单来源名称
                        ,dynamic.if_reback_reach                        --判责自有平台是否达成
                        ,dynamic.reback_apply_time                      --退转件申请时间
                        ,dynamic.intercept_registration_time            --拦截件登记时间
                        ,dynamic.cainiao_plan_sign_time                 --达成率菜鸟规划签收时间
                        ,dynamic.ziyou_plan_sign_time                   --达成率自有规划签收时间
                        ,dynamic.taking_date                            --截单后揽收日期
                        ,dynamic.start_center_code                      --始发中心编码
                        ,dynamic.start_center_name                      --始发中心名称
                        ,dynamic.end_center_code                        --目的中心编码
                        ,dynamic.end_center_name                        --目的中心名称
                        ,1 as scan_site_if_static                       --当前站点是否静态节点
                        ,0 as next_site_if_static                       --下一站点是否静态
                        ,dynamic.customer_code                          --客户编码
                        ,dynamic.customer_name                          --客户名称
                        ,dynamic.scan_site_bagg_user_code               --当前站点建包扫描员编号
                        ,dynamic.scan_site_bagg_user_name               --当前站点建包扫描员名称
                        ,dynamic.pre_site_stop_code                     --经停站点上一站编码
                        ,dynamic.pre_site_stop_name                     --经停站点上一站名称
                        ,dynamic.pre_site_stop_type                     --经停站点上一站类型
                        ,dynamic.scan_site_ship_table_type              --当前站点发件任务号任务类型1干线,2支线
                        ,dynamic.dt
                    from (
                        select *
                        from (
                            select
                                *,row_number() over(partition by waybill_no order by scan_site_no desc) as rn --取最新的轨迹
                            from jms_tmp.dm_whole_decide_duty_dynamic_dt
                            where dt between date_sub('{{ execution_date | cst_ds }}',5) and date_add('{{ execution_date | cst_ds }}',5)
                              and type in (17,18) --限制目的中心集散为最新轨迹
                              and scan_site_no is not null --剔除静停点
                        ) a where a.rn = 1
                    ) dynamic
                    left join (
                        select *
                        from jms_tmp.dm_whole_decide_duty_static_dt route
                         where dt between date_sub('{{ execution_date | cst_ds }}',5) and date_add('{{ execution_date | cst_ds }}',5)
                    ) static on static.waybill_no = dynamic.waybill_no                  --运单相等
                            and static.first_network_code = dynamic.first_network_code  --始发网点
                            and static.last_network_code = dynamic.last_network_code    --目的网点
                     where static.scan_site_plan_no = dynamic.scan_site_no + 1
                ) dynamic
                left join (
                    select *
                    from jms_tmp.dm_whole_decide_duty_static_dt route
                    where dt between date_sub('{{ execution_date | cst_ds }}',5) and date_add('{{ execution_date | cst_ds }}',5)
                ) static on static.waybill_no = dynamic.waybill_no                  --运单相等
                        and static.scan_site_plan_code = dynamic.scan_site_code     --站点相等
                        and static.first_network_code = dynamic.first_network_code  --始发网点
                        and static.last_network_code = dynamic.last_network_code    --目的网点
            ) a
        ) a
    ) a
)

insert overwrite table jms_tmp.dm_whole_decide_duty_dt
select
*
from dm_whole_decide_duty_dt
where first_network_code <> last_network_code
union all
select
     waybill_no                             as waybill_no                                 --运单号
    ,max(platform_effect                  ) as platform_effect                            --自有平台时效标准(兜底路由)
    ,max(ziyou_platform_effect            ) as ziyou_platform_effect                      --自有平台时效标准(推荐路由)
    ,max(if_platform_standard             ) as if_platform_standard                       --自有平台时效是否达成:1达成,0未达成(推荐路由)
    ,max(if_doudi_platform_standard       ) as if_doudi_platform_standard                 --自有平台时效是否达成:1达成,0未达成(兜底路由)
    ,max(cainiao_platform_name            ) as cainiao_platform_name                      --七星潭平台名称
    ,max(cainiao_platform_effect          ) as cainiao_platform_effect                    --七星潭平台时效标准
    ,max(if_cainiao_platform_pres_standard) as if_cainiao_platform_pres_standard          --七星潭平台时效是否达成:1达成,0未达成
    ,max(first_duty_type                  ) as first_duty_type                            --首责任类型:1静态慢于,2静态路由缺失,3地址解析错误,4静态路由变更
    ,max(first_ziyou_duty_type            ) as first_ziyou_duty_type                      --自有平台静态责任:只有地址解析错误
    ,max(cainiao_plan_sign_time           ) as cainiao_plan_sign_time                     --七星潭规划签收时间
    ,max(ziyou_plan_sign_time             ) as ziyou_plan_sign_time                       --自有平台规划签收时间
    ,max(order_source_code                ) as order_source_code                          --订单来源编码
    ,max(order_source_name                ) as order_source_name                          --订单来源名称
    ,max(if_intercept                     ) as if_intercept                               --是否拦截件:1是,0否
    ,max(if_reback                        ) as if_reback                                  --是否退回件:1是,0否
    ,max(if_transfer                      ) as if_transfer                                --是否转寄件:1是,0否
    ,max(if_taking_off                    ) as if_taking_off                              --是否截单后揽收
    ,max(if_taitc                         ) as if_taitc                                   --问题件抬头错标识
    ,max(if_reback_reach                  ) as if_reback_reach                            --判责退转拦截件是否达成
    ,max(start_city_code                  ) as start_city_code                            --始发城市编码
    ,max(start_city_name                  ) as start_city_name                            --始发城市名称
    ,max(sign_city_code                   ) as sign_city_code                             --签收城市编码
    ,max(sign_city_name                   ) as sign_city_name                             --签收城市名称
    ,max(taking_time                      ) as taking_time                                --揽收时间
    ,max(deliver_time                     ) as deliver_time                               --出仓时间
    ,max(aging_sign_time                  ) as aging_sign_time                            --时效签收时间
    ,max(actual_sign_time                 ) as actual_sign_time                           --实际签收时间
    ,max(reback_apply_time                ) as reback_apply_time                          --退转件申请时间
    ,max(intercept_registration_time      ) as intercept_registration_time                --拦截件登记时间
    ,max(economic_circle_name             ) as economic_circle_name                       --经济圈名称
    ,scan_site_no                           as scan_site_no                               --当前站点顺序号
    ,scan_site_code                         as scan_site_code                             --当前站点编码
    ,scan_site_name                         as scan_site_name                             --当前站点名称
    ,scan_site_type                         as scan_site_type                             --当前站点类型
    ,max(scan_site_arrival_time           ) as scan_site_arrival_time                     --当前站点到件时间
    ,max(scan_site_send_time              ) as scan_site_send_time                        --当前站点发件时间
    ,max(scan_site_actual_arrival_time    ) as scan_site_actual_arrival_time              --当前站点实际到车时间
    ,max(scan_site_actual_send_time       ) as scan_site_actual_send_time                 --当前站点实际发车时间
    ,max(scan_site_seal_time              ) as scan_site_seal_time                        --当前站点封车时间
    ,max(scan_site_planned_departure_time ) as scan_site_planned_departure_time           --当前站点计划发车时间
    ,max(scan_site_planned_arrival_time   ) as scan_site_planned_arrival_time             --当前站点计划到车时间
    ,max(scan_site_actual_shipment_no     ) as scan_site_actual_shipment_no               --当前站点实际发车任务号
    ,max(scan_site_actual_shipment_name   ) as scan_site_actual_shipment_name             --当前站点实际发车任务名
    ,max(scan_site_arrival_bagg_code      ) as scan_site_arrival_bagg_code                --当前站点到件包号
    ,max(scan_site_arrival_bagg_name      ) as scan_site_arrival_bagg_name                --当前站点到件包名
    ,max(scan_site_send_bagg_code         ) as scan_site_send_bagg_code                   --当前站点发件包号
    ,max(scan_site_send_bagg_name         ) as scan_site_send_bagg_name                   --当前站点发件包名
    ,max(scan_site_send_bagg_addr_code    ) as scan_site_send_bagg_addr_code              --当前站发件建包目的地编码
    ,max(scan_site_send_bagg_addr_name    ) as scan_site_send_bagg_addr_name              --当前站发件建包目的地名称
    ,max(scan_site_arrival_bagg_addr_code ) as scan_site_arrival_bagg_addr_code           --当前站到件建包目的地编码
    ,max(scan_site_arrival_bagg_addr_name ) as scan_site_arrival_bagg_addr_name           --当前站到件建包目的地名称
    ,max(scan_site_if_stop                ) as scan_site_if_stop                          --当前站点是否经停
    ,max(next_site_code                   ) as next_site_code                             --当前站点实际下一站编码
    ,max(next_site_name                   ) as next_site_name                             --当前站点实际下一站名称
    ,max(next_site_type                   ) as next_site_type                             --当前站点实际下一站类型
    ,max(actual_diff_time                 ) as actual_diff_time                           --当前站点与下一站实际时间差(小时)
    ,max(actual_node                      ) as actual_node                                --实际节点'
    ,max(actual_effect                    ) as actual_effect                              --动态时效'
    ,max(first_network_code               ) as first_network_code                         --始发网点编码
    ,max(first_network_name               ) as first_network_name                         --始发网点名称
    ,max(sign_network_code                ) as sign_network_code                          --签收网点编码
    ,max(sign_network_name                ) as sign_network_name                          --签收网点名称
    ,max(last_network_code                ) as last_network_code                          --目的网点编码
    ,max(last_network_name                ) as last_network_name                          --目的网点名称
    ,max(start_center_code                ) as start_center_code                          --始发中心编码
    ,max(start_center_name                ) as start_center_name                          --始发中心名称
    ,max(end_center_code                  ) as end_center_code                            --目的中心编码
    ,max(end_center_name                  ) as end_center_name                            --目的中心名称
    ,max(plan_last_taking_time            ) as plan_last_taking_time                      --规划最晚揽收时间
    ,max(plan_sign_time                   ) as plan_sign_time                             --规划签收时间
    ,max(scan_site_plan_send_time         ) as scan_site_plan_send_time                   --当前站点规划发车时间
    ,max(scan_site_plan_arrival_time      ) as scan_site_plan_arrival_time                --当前站点规划到车时间
    ,max(scan_site_if_static              ) as scan_site_if_static                        --当前站点是否静态
    ,max(next_site_if_static              ) as next_site_if_static                        --下一站点是否静态
    ,max(total_days                       ) as total_days                                 --T+时效
    ,max(route_id                         ) as route_id                                   --路由唯一键
    ,max(duty_type                        ) as duty_type                                  --菜鸟责任类型
    ,max(ziyou_duty_type                  ) as ziyou_duty_type                            --自有平台责任类型
    ,max(customer_code                    ) as customer_code                              --客户编码  20230824新增
    ,max(customer_name                    ) as customer_name                              --客户名称
    ,max(scan_site_bagg_user_code         ) as scan_site_bagg_user_code                   --当前站点建包扫描员编号
    ,max(scan_site_bagg_user_name         ) as scan_site_bagg_user_name                   --当前站点建包扫描员名称
    ,max(pre_site_code                    ) as pre_site_code                              --当前站点实际上一站编码
    ,max(pre_site_name                    ) as pre_site_name                              --当前站点实际上一站名称
    ,max(pre_site_type                    ) as pre_site_type                              --当前站点实际上一站类型
	,max(scan_site_ship_table_type        ) as scan_site_ship_table_type                  --当前站点发件任务号任务类型1干线,2支线
    ,max(if_static_gx                     ) as if_static_gx                               --是否静态干线
    ,dt
from dm_whole_decide_duty_dt
where first_network_code = last_network_code
group by waybill_no                
        ,scan_site_no                               --当前站点顺序号
        ,scan_site_code                             --当前站点编码
        ,scan_site_name                             --当前站点名称
        ,scan_site_type                             --当前站点类型
        ,dt
distribute by pmod(hash(rand()),80)
;         


